﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using MySql.Data.MySqlClient;

public class AnswerBLL
{
    string connection = WebConfigurationManager.ConnectionStrings["UCNLinkDB"].ConnectionString;

	public AnswerBLL()
	{

	}

    public int InsertAnswer(AnswerProperty ap)
    {
        int result = 0;
        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("INSERT INTO tblAnswer (ansAnswer, QuestionID, ProjectID) VALUES (@ansAnswer, @QuestionID, @ProjectID)");

        cmd.Parameters.Add("@ansAnswer", MySqlDbType.VarChar, 21844);
        cmd.Parameters["@ansAnswer"].Value = ap.AnsAnswer;

        cmd.Parameters.Add("@QuestionID", MySqlDbType.Int32);
        cmd.Parameters["@QuestionID"].Value = ap.QuestionId;

        cmd.Parameters.Add("@ProjectID", MySqlDbType.Int32);
        cmd.Parameters["@ProjectID"].Value = ap.ProjectId;

        conn.Open();

        cmd.Connection = conn;

        result = cmd.ExecuteNonQuery();
        cmd.Dispose();
        conn.Close();
        return result;
    }

    public List<AnswerProperty> getAnswersByProjectID(int proId)
    {

        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("SELECT tblAnswer.ansAnswer, tblQuestion.queQuestion FROM tblAnswer INNER JOIN tblQuestion ON tblAnswer.QuestionID = tblQuestion.ID WHERE tblAnswer.ProjectID = @ProjectID", conn);

        cmd.Parameters.Add("@ProjectID", MySqlDbType.Int32);
        cmd.Parameters["@ProjectID"].Value = proId;

        List<AnswerProperty> allAP = new List<AnswerProperty>();

        conn.Open();
        cmd.Connection = conn;

        MySqlDataReader reader = cmd.ExecuteReader();


        if (reader.HasRows)
        {
            foreach (Object read in reader)
            {
                AnswerProperty ap = new AnswerProperty();
                ap.AnsAnswer = reader["ansAnswer"].ToString();
                ap.QuestionString = reader["queQuestion"].ToString();
                allAP.Add(ap);
            }
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return allAP;
    }
}